package com.util;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.collections.map.CaseInsensitiveMap;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DruidUtil {
    private static DataSource druidDataSource;
    private static Logger log= Logger.getLogger(DruidUtil.class);
    static{
        druidDataSource=Druidinit();
    }
    private static DataSource Druidinit(){
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(PropertiesUtil.getStringPro("spring.datasource.url"));
        datasource.setUsername(PropertiesUtil.getStringPro("spring.datasource.username"));
        datasource.setPassword(PropertiesUtil.getStringPro("spring.datasource.password"));
        datasource.setDriverClassName(PropertiesUtil.getStringPro("spring.datasource.driverclassname"));
        //configuration
        datasource.setInitialSize(PropertiesUtil.getintPro("spring.datasource.initialSize"));
        datasource.setMinIdle(PropertiesUtil.getintPro("spring.datasource.minIdle"));
        datasource.setMaxActive(PropertiesUtil.getintPro("spring.datasource.maxActive"));
        datasource.setMaxWait(PropertiesUtil.getintPro("spring.datasource.maxWait"));
        datasource.setTimeBetweenEvictionRunsMillis(PropertiesUtil.getintPro("spring.datasource.timeBetweenEvictionRunsMillis"));
        datasource.setMinEvictableIdleTimeMillis(PropertiesUtil.getintPro("spring.datasource.minEvictableIdleTimeMillis"));
        datasource.setValidationQuery(PropertiesUtil.getStringPro("spring.datasource.validationQuery"));
        datasource.setTestWhileIdle(PropertiesUtil.getbooleanPro("spring.datasource.testWhileIdle"));
        datasource.setTestOnBorrow(PropertiesUtil.getbooleanPro("spring.datasource.testOnBorrow"));
        datasource.setTestOnReturn(PropertiesUtil.getbooleanPro("spring.datasource.testOnReturn"));
        datasource.setPoolPreparedStatements(PropertiesUtil.getbooleanPro("spring.datasource.poolPreparedStatements"));
        datasource.setMaxPoolPreparedStatementPerConnectionSize(PropertiesUtil.getintPro("spring.datasource.maxPoolPreparedStatementPerConnectionSize"));
       // datasource.setConnectionProperties(PropertiesUtil.getStringPro("spring.datasource.connectionProperties"));
        return datasource;
    }
    public static Connection getConnection() {
        Connection conn = null;
        try {
            if (druidDataSource == null) {
                druidDataSource = Druidinit();
            }
            conn = druidDataSource.getConnection();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return conn;
    }
    public static void closeConnection(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (conn != null) {
                        conn.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public static boolean batch(List<String> sqlList, List<Object[]> paramList){
        Connection conn = null;

        //验证参数是否可用
        if(null == sqlList || null == paramList){
            if(log.isDebugEnabled()){
                log.debug("SQL语句列表为空，或者，参数列表为空");
            }
            return false;
        }

        if(sqlList.size() <= 0 || paramList.size() <= 0){
            if(log.isDebugEnabled()){
                log.debug("SQL语句列表为空，或者，参数列表为空");
            }
            return false;
        }

        //SQL和参数表要匹酿
        if(sqlList.size() != paramList.size()){
            if(log.isDebugEnabled()){
                log.debug("SQL语句列表数目 <>参数列表数目");
            }
            return false;
        }

        //取到连接
        conn = getConnection();

        try {
            conn.setAutoCommit(false);

            for(int i=0;i<sqlList.size();i++){
                String strSQL = sqlList.get(i);
                Object[] params = paramList.get(i);

                exec(conn, strSQL, params);
            }

            conn.commit();
            return true;
        } catch (Exception e) {
            // TODO: handle exception
            log.error("执行SQL语句时发生");

            try {
                if(log.isEnabledFor(Level.toLevel("warn"))){
                    log.warn("进行事物回滚");
                }
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                log.error("事务回滚时发生了错误", e1);
            }
        }finally{
            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                log.error("setAutoCommit=true时失败", e);
            }

            ConnHelper.close(conn);
        }

        return false;
    }

    /**
     * @param conn
     * @param strSQL
     * @param params
     * @return
     * @throws Exception
     *
     * 加入参数校验。当参数校验失败时，抛出错误〿
     */
    private static boolean exec(Connection conn, String strSQL,
                                Object[] params) throws Exception {
        // TODO Auto-generated method stub
        if(null == strSQL){
            throw new Exception("没有给出SQL语句");
        }

        String trimdSQL = strSQL.trim().toLowerCase();
        if(trimdSQL.length() <= 0){
            throw new Exception("没有给出参数列表");
        }

        if(trimdSQL.startsWith("insert")){
            execInsert(conn, strSQL, params);
        }else if(trimdSQL.startsWith("update")){
            execUpdate(conn, strSQL, params);
        }else{
            execDefault(conn, strSQL, params);
            //throw new Exception("事务方法碰到暂时不能支持的sql语句。目前仅支持insert、update语句〿);
        }

        return true;
    }

    private static void execDefault(Connection conn, String strSQL, Object[] params) throws Exception{
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(strSQL);
            for(int i=0;i<params.length;i++){
                DetermineParamType.determine(ps, i+1, params[i]);
            }

            ps.execute();
        } catch (Exception e) {
            log.error("执行以下SQL语句发生错误:"+toString(strSQL, params), e);
            throw e;
        }finally{
            ConnHelper.close(ps);
        }
    }

    private static int execUpdate(Connection conn, String strSQL, Object[] params) throws Exception{
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(strSQL);
            for(int i=0;i<params.length;i++){
                DetermineParamType.determine(ps, i+1, params[i]);
            }

            return ps.executeUpdate();
        } catch (Exception e) {
            log.error("执行以下SQL语句发生错误:"+toString(strSQL, params), e);
            throw e;
        }finally{
            ConnHelper.close(ps);
        }
    }

    private static int execInsert(Connection conn, String strSQL, Object[] params) throws Exception{
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(strSQL);
            for(int i=0;i<params.length;i++){
                DetermineParamType.determine(ps, i+1, params[i]);
            }

            return ps.executeUpdate();
        } catch (Exception e) {
            log.error("执行以下SQL语句发生错误:"+toString(strSQL, params), e);
            throw e;
        }finally{
            ConnHelper.close(ps);
        }
    }

    /* (non-Javadoc)
     * @see net.tycmc.bulb.common.dao.ITransactionDAO#batch(java.util.List)
     */
    public static boolean batch(List<String> sqlList){
        Connection connection=null;
        Statement stmt = null;

        //棿ߥ参数
        if (sqlList==null || sqlList.size()<=0){
            if(log.isDebugEnabled()){
                log.debug("SQL语句列表为空");
            }
            return false;
        }

        for(String sqlStr : sqlList){
            if (sqlStr == null || "".equals(sqlStr.trim())){
                if(log.isDebugEnabled()){
                    log.debug("SQL语句列表中某条SQL语句为空");
                }
                return false;
            }
        }

        connection = getConnection();

        //将自动提交方式设为fasle，娀果设置失败关闭连接，返回false〿
        try {
            connection.setAutoCommit(false);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            log.error("setAutoCommit=false时失败", e);
            ConnHelper.close(connection);
            return false;
        }

        try {
            stmt=connection.createStatement();

            //批量添加语句
            for (String sqlStr : sqlList){
                stmt.addBatch(sqlStr);
            }

            //丿̡性提交语叿
            int[] updateCounts = stmt.executeBatch();
            connection.commit();

            return true;
        } catch (Exception ex){
            log.error("执行下面的语句时发生错误＿+sqlList.toString()", ex);//记录错误到日忿

            //批量提交失败，进行事物回滿
            try {
                if(log.isEnabledFor(Level.toLevel("warn"))){
                    log.warn("弿ƾ进行事物回滚");
                }
                connection.rollback();
            } catch (Exception ex1){
                log.error("事务回滚时发生了错误", ex1);
            }
        }finally{
            //setAutoCommit=true
            try {
                connection.setAutoCommit(true);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                log.error("setAutoCommit=true时失败", e);
            }

            //close
            ConnHelper.close(stmt);
            ConnHelper.close(connection);
        }

        return false;
    }

    private static String toString(String strSQL, Object[] params){
        StringBuilder sb = new StringBuilder();
        sb.append(strSQL);
        sb.append("\n");
        for(Object param:params){
            if(param instanceof String){
                sb.append((String)param);
            }else{
                sb.append(param);
            }
            sb.append("\t");
        }

        return sb.toString();
    }
    public static int execUpdate(String strSQL, Object[] params){
        Connection conn = getConnection();
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(strSQL);
            for(int i=0;i<params.length;i++){
                DetermineParamType.determine(ps, i+1, params[i]);
            }

            return ps.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
            log.error(toString(strSQL, params), e);
        }finally{
            ConnHelper.close(ps);
            ConnHelper.close(conn);
        }

        return -1;
    }

    /* (non-Javadoc)
     * @see net.tycmc.bulb.common.dao.IBaseDAO#execInsert(java.lang.String, java.lang.Object[])
     */
    public static int execInsert(String strSQL, Object[] params){
        Connection conn = getConnection();
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(strSQL);
            for(int i=0;i<params.length;i++){
                DetermineParamType.determine(ps, i+1, params[i]);
            }

            return ps.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
            log.error(toString(strSQL, params), e);
        }finally{
            ConnHelper.close(ps);
            ConnHelper.close(conn);
        }

        return -1;
    }

    /* (non-Javadoc)
     * @see net.tycmc.bulb.common.dao.IBaseDAO#execQuery(java.lang.String, java.lang.Object[])
     */
    @SuppressWarnings("unchecked")
    public List<Map<String,Object>> execQuery(String strSQL, Object[] params){
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String,Object>> rsList = new ArrayList<Map<String,Object>>(1);

        try {
            ps = conn.prepareStatement(strSQL);
            for(int i=0;i<params.length;i++){
                DetermineParamType.determine(ps, i+1, params[i]);
            }

            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            while(rs.next()){
                int cc = metaData.getColumnCount();
                Map<String,Object> mapRS =new CaseInsensitiveMap();// new HashMap<String,Object>();
                for(int i=1;i<=cc;i++){
                    mapRS.put(metaData.getColumnName(i), rs.getObject(i));
                }
                rsList.add(mapRS);
            }

            return rsList;
        } catch (Exception e) {
            log.error(toString(strSQL, params), e);
        }finally{
            ConnHelper.close(rs);
            ConnHelper.close(ps);
            ConnHelper.close(conn);
        }

        return rsList;
    }
    /* (non-Javadoc)
     * @see net.tycmc.bulb.common.dao.IBaseDAO#execQuery(java.lang.String, java.lang.Object[])
     */
    @SuppressWarnings("unchecked")
    public static List<Map<String,Object>> execQuery(String strSQL){
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String,Object>> rsList = new ArrayList<Map<String,Object>>(1);

        try {
            ps = conn.prepareStatement(strSQL);

            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            while(rs.next()){
                int cc = metaData.getColumnCount();
                Map<String,Object> mapRS =new CaseInsensitiveMap();// new HashMap<String,Object>();
                for(int i=1;i<=cc;i++){
                    mapRS.put(metaData.getColumnName(i), rs.getObject(i));
                }
                rsList.add(mapRS);
            }

            return rsList;
        } catch (Exception e) {
            log.error(toString(strSQL, new Object[]{}), e);
        }finally{
            ConnHelper.close(rs);
            ConnHelper.close(ps);
            ConnHelper.close(conn);
        }

        return rsList;
    }

    /* (non-Javadoc)
     * @see net.tycmc.bulb.common.dao.IBaseDAO#execQueryOne(java.lang.String, java.lang.Object[])
     */
    @SuppressWarnings("unchecked")
    public static Map<String,Object> execQueryOne(String strSQL, Object[] params){
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = conn.prepareStatement(strSQL);
            for(int i=0;i<params.length;i++){
                DetermineParamType.determine(ps, i+1, params[i]);
            }

            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            if(rs.next()){
                int cc = metaData.getColumnCount();
                Map<String,Object> mapRS = new CaseInsensitiveMap();//new HashMap<String,Object>();
                for(int i=1;i<=cc;i++){
                    mapRS.put(metaData.getColumnName(i), rs.getObject(i));
                }
                return mapRS;
            }
        } catch (Exception e) {
            log.error(toString(strSQL, params), e);
        }finally{
            ConnHelper.close(rs);
            ConnHelper.close(ps);
            ConnHelper.close(conn);
        }

        return new HashMap<String,Object>();
    }
}
